1. Field of the Invention
The present invention relates to methods, apparatus and computer-code for database administration.
2. Related Art
It is known that various operations and SQL statement executions can create sort activity in the database. Below is a list of common SQL commands/clauses that may trigger a sort operation:
CREATE INDEX, ALTER INDEX . . . REBUILD
DISTINCT
ORDER BY
GROUP BY
UNION
INTERSECT
MINUS
Certain joins
Certain subqueries
In order to carry out the sort operation, memory and processing power are required. Many relational databases operate as follows: (i) in the event that enough volatile memory is “available” to carry out the sort of the retrieved data, the sort is carried out in volatile memory; (ii) otherwise, the sort is carried out when the data to be sorted resides, at least in part, in volatile memory. For at least some relational databases, the amount of volatile memory that may be used for sorting operations may be defined by one or more “configuration parameters” that is controllable by the database administrator (DBA).
In the event that the value of this parameter is “too low,” then it is possible that too many ‘disk-sort’ operations may be carried out. On the other hand, if the value of this parameter is set “too high,” then this may require deploying additional physical memory and/or this may cause a situation where the amount of RAM available for other purposes may be limited.
For the particular case of Oracle®, the area of RAM in which data may be sorted is known as the system global area (SGA). For some versions of Oracle®, PGA_AGGREGATE_TARGET and/or one or more other parameters may be used by a database administrator (DBA) to control the amount of RAM available for sort operation(s) required by database statement(s). For Oracle® or any other relational database, the disk sort operations may be carried out when the data is in a “temporary file.”
Unfortunately, there are situations where effecting sorts on disk (i.e. as opposed to in RAM) when executing database statements may significantly slow the speed of execution of one or more database statement(s), and may negatively impact database performance.
There is an ongoing need for tools and techniques for detecting when and to what is data, and for providing appropriate ‘tuning’ guidance to database administrators in order to properly configure the database (i.e. by increasing the amount of RAM available for sorting data and/or by re-writing one or more database statements to reduce the need for disk-sort and/or by upgrading a disk on which data is sorted and/or in any other manner) to improve database performance.